package com.neuedu.oasystem.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.neuedu.oasystem.dao.IBuyPlanDao;
import com.neuedu.oasystem.entity.BuyPlan;
import com.neuedu.oasystem.util.DBHelper;

public class BuyPlanDaoImpl extends BaseDao implements IBuyPlanDao {
	@Override
	public boolean add(BuyPlan param) {
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		try {
			String sql = "insert into Buy_Plan(ap_No,ap_Date,EMP_NAME,assets_No,assets_Price,assets_Number,totaly_Price,approval,register) values(?,?,?,?,?,?,?,?,?)";
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, param.getApNo());
			stmt.setTimestamp(2, new java.sql.Timestamp(param.getApDate().getTime()));
			stmt.setString(3, param.getEmpName());
			stmt.setInt(4, param.getAssetsNo());
			stmt.setDouble(5, param.getAssetsPrice());
			stmt.setInt(6, param.getAssetsNumber());
			stmt.setDouble(7, param.getAssetsPrice()*param.getAssetsNumber());
			stmt.setString(8, param.getApproval());
			stmt.setString(9, param.getRegister().toLowerCase());
			int count = stmt.executeUpdate();
			if (count == 1) {
				return true;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(null, stmt, null);
		}
		return false;
	}

	@Override
	public boolean edit(BuyPlan param) {
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		try {
			String sql = "update Buy_Plan set ap_Date=?,EMP_NAME=?,assets_No=?,assets_Price=?,assets_Number=?,totaly_Price=?,approval=?,register=? where  ap_No=? ";
			stmt = conn.prepareStatement(sql);
			stmt.setInt(9, param.getApNo());
			stmt.setTimestamp(1, new java.sql.Timestamp(param.getApDate().getTime()));
			stmt.setString(2, param.getEmpName());
			stmt.setInt(3, param.getAssetsNo());
			stmt.setDouble(4, param.getAssetsPrice());
			stmt.setInt(5, param.getAssetsNumber());
			stmt.setDouble(6, param.getTotalyPrice());
			stmt.setString(7, param.getApproval());
			stmt.setString(8, param.getRegister());
			int count = stmt.executeUpdate();
			if (count == 1) {
				return true;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(null, stmt, null);
		}
		return false;
	}

	@Override
	public boolean deleteById(int apNo) {
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;

		try {
			String sql = "delete from Buy_Plan where ap_No=?";
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, apNo);
			int count = stmt.executeUpdate();
			if (count == 1) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(null, stmt, null);
		}

		return false;
	}

	@Override
	public BuyPlan findById(int no) {
		Connection conn = getConnection();
		PreparedStatement stmt = null;
		BuyPlan buyPlan = null;
		ResultSet rs = null;

		try {

			String sql = "select ap_No,ap_Date,EMP_NAME,assets_No,assets_Price,assets_Number,totaly_Price,approval,register from buy_plan  where ap_No=?";
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, no);
			rs = stmt.executeQuery();
			if (rs.next()) {
				int apNo = rs.getInt(1);
				Date apDate = rs.getTimestamp("ap_Date");
				String apName = rs.getString("EMP_NAME");
				int assetsNo = rs.getInt("assets_No");
				double assetsPrice = rs.getDouble("assets_Price");
				int assetsNumber = rs.getInt("assets_Number");
				double totalyPrice = rs.getDouble("totaly_Price");
				String approval = rs.getString("approval");
				String register = rs.getString("register");
				buyPlan = new BuyPlan(apNo, apDate, apName, 
						assetsNo, assetsPrice, assetsNumber,
						totalyPrice, approval, register);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(rs, stmt, null);
		}
		return buyPlan;
	}

	@Override
	public List<BuyPlan> findAll() {
		List<BuyPlan> list = new ArrayList<BuyPlan>();
		Connection conn = getConnection();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		BuyPlan buyPlan;

		try {
			conn = getConnection();
			String sql = "select ap_No,ap_Date,EMP_NAME,assets_No,assets_Price,assets_Number,totaly_Price,approval,register from buy_plan";
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				int apNo = rs.getInt(1);
				Date apDate = rs.getTimestamp("ap_Date");
				String apName = rs.getString("EMP_NAME");
				int assetsNo = rs.getInt("assets_No");
				double assetsPrice = rs.getDouble("assets_Price");
				int assetsNumber = rs.getInt("assets_Number");
				double totalyPrice = rs.getDouble("totaly_Price");
				String approval = rs.getString("approval");
				String register = rs.getString("register");
				buyPlan = new BuyPlan(apNo, apDate, apName, 
						assetsNo, assetsPrice, assetsNumber,
						totalyPrice, approval, register);
				list.add(buyPlan);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(rs, stmt, null);
		}

		return list;
	}

	@Override
	public List<BuyPlan> findByExample(BuyPlan param) {
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		List<BuyPlan> list = new ArrayList<BuyPlan>();
		ResultSet rs = null;
		BuyPlan buyPlan = null;
		try {
			String sql = "select ap_No,ap_Date,EMP_NAME,assets_No,assets_Price,assets_Number,totaly_Price,approval,register from buy_plan where EMP_NAME like ?";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, "%" + param.getEmpName() + "%");
			rs = stmt.executeQuery();
			while (rs.next()) {
				int apNo = rs.getInt(1);
				Date apDate = rs.getTimestamp("ap_Date");
				String apName = rs.getString("EMP_NAME");
				int assetsNo = rs.getInt("assets_No");
				double assetsPrice = rs.getDouble("assets_Price");
				int assetsNumber = rs.getInt("assets_Number");
				double totalyPrice = rs.getDouble("totaly_Price");
				String approval = rs.getString("approval");
				String register = rs.getString("register");
				buyPlan = new BuyPlan(apNo, apDate, apName, 
						assetsNo, assetsPrice, assetsNumber,
						totalyPrice, approval, register);
				list.add(buyPlan);

			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(rs, stmt, null);
		}

		return list;
	}

	@Override
	public List<BuyPlan> findByPager(int page, int size) {
		List<BuyPlan> list = new ArrayList<BuyPlan>();
		Connection conn = getConnection();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "select * from"
					+ "(select rownum rn,ap_No,ap_Date,EMP_NAME,assets_No,assets_Price,assets_Number,totaly_Price,approval,register from buy_plan where rownum<=?)where rn>? ";

			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, page * size);
			stmt.setInt(2, (page - 1) * size);
			rs = stmt.executeQuery();
			BuyPlan buyPlan = null;
			while (rs.next()) {
				int apNo = rs.getInt(2);
				Date apDate = rs.getTimestamp("ap_Date");
				String apName = rs.getString("EMP_NAME");
				int assetsNo = rs.getInt("assets_No");
				double assetsPrice = rs.getDouble("assets_Price");
				int assetsNumber = rs.getInt("assets_Number");
				double totalyPrice = rs.getDouble("totaly_Price");
				String approval = rs.getString("approval");
				String register = rs.getString("register");
				buyPlan = new BuyPlan(apNo, apDate, apName, 
						assetsNo, assetsPrice, assetsNumber,
						totalyPrice, approval, register);
				list.add(buyPlan);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(rs, stmt, null);
		}
		return list;
	}

	@Override
	public int deleteByIds(int[] nos) {
		int result = 0;
		Connection conn = getConnection();
		PreparedStatement stmt = null;
		try {
			conn.setAutoCommit(false);
			String sql = "delete from Buy_Plan where ap_No = ?";
			stmt = conn.prepareStatement(sql);
			for (int apNo : nos) {
				stmt.setInt(1, apNo);
				stmt.addBatch();
			}
			int[] counts = stmt.executeBatch();
			for (int i : counts) {
				if (i == -2) {
					result++;
				}
			}
			if (result == nos.length) {
				conn.commit();
				conn.setAutoCommit(true);
			} else {
				conn.rollback();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			if (conn != null) {
				try {
					conn.rollback();
					conn.setAutoCommit(true);
				} catch (SQLException e1) {
					e1.printStackTrace();
				}

			}
		} finally {
			DBHelper.closeAll(null, stmt, null);
		}
		return result;
	}

	@Override
	public int addAll(List<BuyPlan> param) {
		int result = 0;
		Connection conn = getConnection();
		PreparedStatement stmt = null;
		try {
			conn.setAutoCommit(false);
			String sql = "insert into Buy_Plan(ap_No,ap_Date,EMP_NAME,assets_No,assets_Price,assets_Number,totaly_Price,approval,register) values(?,?,?,?,?,?,?,?,?)";
			stmt = conn.prepareStatement(sql);
			for (BuyPlan i : param) {
				stmt.setInt(1, i.getApNo());
				stmt.setTimestamp(2, new java.sql.Timestamp(i.getApDate().getTime()));
				stmt.setString(3, i.getEmpName());
				stmt.setInt(4, i.getAssetsNo());
				stmt.setDouble(5, i.getAssetsPrice());
				stmt.setInt(6, i.getAssetsNumber());
				stmt.setDouble(7, i.getTotalyPrice());
				stmt.setString(8, i.getApproval());
				stmt.setString(9, i.getRegister());
				stmt.addBatch();
			}
			int[] counts = stmt.executeBatch();
			for (int i : counts) {
				if (i == -2) {
					result++;
				}
			}
			if (result == param.size()) {
				conn.commit();
				conn.setAutoCommit(true);
			} else {
				conn.rollback();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			if (conn != null) {
				try {
					conn.rollback();
					conn.setAutoCommit(true);
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
		} finally {
			DBHelper.closeAll(null, stmt, null);
		}
		return result;
	}

	@Override
	public int editAll(List<BuyPlan> param) {
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		int result = 0;
		try {
			conn.setAutoCommit(false);
			String sql = "update Buy_Plan set ap_Date=?,EMP_NAME=?,assets_No=?,assets_Price=?,assets_Number=?,totaly_Price=?,approval=?,register=? where  ap_No=?";
			stmt = conn.prepareStatement(sql);
			for (BuyPlan i : param) {
				stmt.setInt(9, i.getApNo());
				stmt.setTimestamp(1, new java.sql.Timestamp(i.getApDate().getTime()));
				stmt.setString(2, i.getEmpName());
				stmt.setInt(3, i.getAssetsNo());
				stmt.setDouble(4, i.getAssetsPrice());
				stmt.setInt(5, i.getAssetsNumber());
				stmt.setDouble(6, i.getTotalyPrice());
				stmt.setString(7,i.getApproval());
				stmt.setString(8,i.getRegister());
				stmt.addBatch();

			}
			int[] counts = stmt.executeBatch();
			for (int i : counts) {
				if (i == -2) {
					result++;
				}
			}
			if (result == param.size()) {
				conn.commit();
				conn.setAutoCommit(true);
			} else {
				conn.rollback();

			}

		} catch (SQLException e) {
			e.printStackTrace();
			if (conn != null) {
				try {
					conn.rollback();
					conn.setAutoCommit(true);
				} catch (SQLException e1) {
					e1.printStackTrace();
				}

			}
		} finally {
			DBHelper.closeAll(null, stmt, null);
		}

		return result;
	}
	/**
	 * 查询审批成功的计划
	 * 
	 * 
	 */
	@Override
	public List<BuyPlan> findByApproval( ) {
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		List<BuyPlan> list = new ArrayList<BuyPlan>();
		ResultSet rs = null;
		BuyPlan buyPlan = null;
		try {
			String sql = "select ap_No,ap_Date,EMP_NAME,assets_No,assets_Price,assets_Number,totaly_Price,approval,register from buy_plan where approval='1'";
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				int apNo = rs.getInt(1);
				Date apDate = rs.getTimestamp("ap_Date");
				String apName = rs.getString("EMP_NAME");
				int assetsNo = rs.getInt("assets_No");
				double assetsPrice = rs.getDouble("assets_Price");
				int assetsNumber = rs.getInt("assets_Number");
				double totalyPrice = rs.getDouble("totaly_Price");
				String approval1 = rs.getString("approval");
				String register = rs.getString("register");
				buyPlan = new BuyPlan(apNo, apDate, apName, 
						assetsNo, assetsPrice, assetsNumber,
						totalyPrice, approval1, register);
				list.add(buyPlan);

			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(rs, stmt, null);
		}

		return list;
	}
	
	/**
	 * 
	 * 
	 *
	 */
	@Override
	public List<BuyPlan> findByRegister() {
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		List<BuyPlan> list = new ArrayList<BuyPlan>();
		ResultSet rs = null;
		BuyPlan buyPlan = null;
		try {
			String sql = "select ap_No,ap_Date,EMP_NAME,assets_No,assets_Price,assets_Number,totaly_Price,approval,register from buy_plan where register= 'y'";
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				int apNo = rs.getInt(1);
				Date apDate = rs.getTimestamp("ap_Date");
				String apName = rs.getString("EMP_NAME");
				int assetsNo = rs.getInt("assets_No");
				double assetsPrice = rs.getDouble("assets_Price");
				int assetsNumber = rs.getInt("assets_Number");
				double totalyPrice = rs.getDouble("totaly_Price");
				String approval1 = rs.getString("approval");
				String register1 = rs.getString("register");
				buyPlan = new BuyPlan(apNo, apDate, apName, 
						assetsNo, assetsPrice, assetsNumber,
						totalyPrice, approval1, register1);
				list.add(buyPlan);

			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(rs, stmt, null);
		}

		return list;
	}

	@Override
	public boolean editApproval(BuyPlan param) throws RuntimeException {
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		try {
			String sql = "update Buy_Plan set approval=? where  ap_No=? ";
			stmt = conn.prepareStatement(sql);
			stmt.setInt(2, param.getApNo());
			stmt.setString(1, param.getApproval());
			int count = stmt.executeUpdate();
			if (count == 1) {
				return true;
			}

		} catch (SQLException e) {
			throw new RuntimeException();
		} finally {
			DBHelper.closeAll(null, stmt, null);
		}
		return false;
	}

	@Override
	public boolean editRegister(BuyPlan param) {
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		try {
			String sql = "update Buy_Plan set register=? where  ap_No=? ";
			stmt = conn.prepareStatement(sql);
			stmt.setInt(2, param.getApNo());
			stmt.setString(1, param.getRegister().toLowerCase());
			int count = stmt.executeUpdate();
			if (count == 1) {
				return true;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(null, stmt, null);
		}
		return false;
	}
	
	
}